const { DB } = require("../config")
const { listObjToTreeObj } = require("../utils")

/**
 *
 * @description 关联所有相关表进行联合查询
 * @param {*} isSingle 是否是查询单个
 * @param {*} options 查询时携带的参数配置
 */
async function getOrders(isSingle, options) {
	const { id, managerId, prodId, status, pageSize = 5, pageNum = 1 } = options
	let args = []

	const childSql =
		"SELECT " +
		" odp.ordersId AS odsId, " +
		" GROUP_CONCAT( odp.`productNum` SEPARATOR ',' ) AS productsNumb, " +
		// 产品信息
		" GROUP_CONCAT( products.`id` SEPARATOR ',' ) AS productsId," +
		" GROUP_CONCAT( products.`name` SEPARATOR ',' ) AS productsName," +
		" GROUP_CONCAT( products.`guidPrice` SEPARATOR ',' ) AS productsGuidPrice," +
		" GROUP_CONCAT( products.`price` SEPARATOR ',' ) AS productsPrice," +
		" GROUP_CONCAT( products.`productType` SEPARATOR ',' ) AS productsProductType," +
		" GROUP_CONCAT( products.`applicableCrops` SEPARATOR ',' ) AS productsapplicableCrops, " +
		" GROUP_CONCAT( products.`effectFeatures` SEPARATOR ',' ) AS productsEffectFeatures, " +
		" GROUP_CONCAT( products.`mainComponents` SEPARATOR ',' ) AS productsMainComponents, " +
		" GROUP_CONCAT( products.`applicationMethod` SEPARATOR ',' ) AS productsApplicationMethod, " +
		" GROUP_CONCAT( products.`applicationAmount` SEPARATOR ',' ) AS productsApplicationAmount " +
		" FROM `orders_products` AS odp " +
		" INNER JOIN `products` ON odp.productId=products.id " +
		" GROUP BY odp.ordersId"

	const baseSql =
		"SELECT " +
		" orders.*, odp.*, " +
		// 订单状态
		" tags.`id` AS tagsId, " +
		" tags.`name` AS tagsName, " +
		" tags.`color` AS tagsColor, " +
		" tags.`type` AS tagsType, " +
		// 客户信息
		" ct.`id` AS customerId, " +
		" ct.`name`  AS customerName, " +
		" ct.`avatar` AS customerAvatar, " +
		" ct.`age` AS customerAge, " +
		" ct.`gender` AS customerGender, " +
		" ct.`phone` AS customerPhone " +
		" FROM `orders`" +
		" INNER JOIN `tags` ON orders.status = tags.id" +
		" INNER JOIN `customers` AS ct ON orders.managerId = ct.id " +
		" INNER JOIN ( " +
		childSql +
		" ) AS odp ON orders.id = odp.odsId "

	let multiple = ""

	try {
		if (isSingle) {
			multiple = "HAVING orders.id=?"
			args.push(id)
		} else {
			const offset = (pageNum - 1) * pageSize

			multiple += "HAVING"
			if (status && status !== "0") {
				multiple += " orders.status = ? AND"
				args.push(status)
			}
			if (managerId && managerId !== "0") {
				multiple += " orders.managerId = ? AND"
				args.push(managerId)
			}

			if (prodId && prodId !== "0") {
				const ids = await getOrdersIds(prodId)
				// " + ids + "
				multiple += " orders.id IN ( ? ) AND"
				args.push(ids)
			}

			if (args.length > 0) {
				multiple = multiple.slice(0, -3)
			} else {
				multiple = multiple.slice(6)
			}

			multiple += ` LIMIT ${pageSize} OFFSET ${offset} `
		}

		const sql = baseSql + multiple

		const [rows] = await DB.execute(sql, args)

		const newList = rows.map((row) => {
			const item = listObjToTreeObj(row, [
				{ prefix: "products", id: "productsId" },
				{ prefix: "customer", id: "customerId" },
				{ prefix: "tags", id: "tagsId" }
			])

			item.status = item.tags[0]
			delete item.tags

			item.customer = item.customer[0]

			return item
		})

		return newList
	} catch (error) {
		console.error("error", error)
		throw error
	}
}

// 获取产品对应的订单
async function getOrdersIds(prodId) {
	const sql =
		"SELECT op.ordersId FROM `orders_products` AS op WHERE op.productId = ?"
	const [rows] = await DB.execute(sql, [prodId])

	const ids = rows.map((item) => item.ordersId).join(",")
	return ids
}

/**
 *
 * @description 简单的单表查询，不连表查询
 * @param {*} isSingle 是否是查询单个
 * @param {*} options 查询时携带的参数配置
 */
async function getSimpleOrders(options) {
	const baseSql = "SELECT * FROM `customers` LIMIT ? OFFSET ? "

	const { pageSize = 5, pageNum = 1 } = options

	try {
		const offset = (pageNum - 1) * pageSize

		const [rows] = await DB.execute(baseSql, [pageSize, offset])

		return rows
	} catch (error) {
		console.error("error", error)
		throw error
	}
}

module.exports = {
	getOrders,
	getSimpleOrders
}
